from sqlalchemy import create_engine, MetaData
from sqlalchemy import Table, Column, ForeignKey
from sqlalchemy import String, DateTime, Integer, Text

metadata = MetaData()

uploader = Table(
    'uploader', metadata,
    Column('uid', Integer(), primary_key=True),
    Column('name', String(255), nullable=False)
)

video = Table(
    'video', metadata,
    Column('av', Integer(), primary_key=True),
    Column('bv', String(20), nullable=False, index=True, unique=True),
    Column('comment_count', Integer(), nullable=False),
    Column('play_count', Integer(), nullable=False),
    Column('title', String(255), nullable=False),
    Column('description', Text(), nullable=False),
    Column('uploader_id', Integer(), ForeignKey(column='uploader.uid', ondelete='CASCADE'), nullable=False),
    Column('upload_time', DateTime(), nullable=False),
)

comments = Table(
    'comments', metadata,
    Column('rp_id', Integer(), primary_key=True),
    Column('video_id', Integer(), ForeignKey(column='video.av', ondelete='CASCADE'), nullable=False, index=True),
    Column('likes', Integer(), nullable=False),
    Column('root_comment', Integer(), ForeignKey(column='comments.rp_id', ondelete='CASCADE'), nullable=True),
    Column('content', String(255), nullable=False),
    Column('comment_time', DateTime(), nullable=False),
)

dm = Table(
    'dm', metadata,
    Column('id', Integer(), autoincrement=True, primary_key=True),
    Column('video_id', Integer(), ForeignKey(column='video.av', ondelete='CASCADE'), nullable=False, index=True),
    Column('content', String(255), nullable=False),
    Column('property', String(255), nullable=False),
)

if __name__ == '__main__':
    engine = create_engine('sqlite:///../bilibili.db', echo=True, encoding='utf-8')
    metadata.create_all(engine)
